package com.project.thisDatabase.excel;

import com.project.thisCommon.common.ConvertEntity;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Component;

import java.io.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.TreeMap;

@Component
public class ExcelExecutor {

    public final static String EXCEL2003 = "xls";
    public final static String EXCEL2007 = "xlsx";

    public List<Map<String, Object>> read(InputStream fs, String type, String sheet, boolean head) throws IOException {
        List<Map<String, Object>> res = new ArrayList<>();
        Workbook workbook = createWorkbook(type, fs);

        if (null != workbook) {
            Sheet st = null;
            if (StringUtils.isNotBlank(sheet)){
                st = workbook.getSheet(sheet);
            } else {
                st = workbook.getSheetAt(0);
            }
            Map<String, Object> head_map = new TreeMap<>();
            Integer rowNum = st.getFirstRowNum();
            if (head) {
                head_map = readCell(st.getRow(rowNum++), head_map, StringUtils.EMPTY);
            }

            while(st.getLastRowNum() >= rowNum) {
                Map<String, Object> cell_map = readCell(st.getRow(rowNum++), head_map, "C");
                if (cell_map.size() > 0) {
                    res.add(cell_map);
                }
            }
        }

        return res;
    }

    public List<Map<String, Object>> read(String path, boolean head) throws IOException {
        return read(path, null, head);
    }

    public List<Map<String, Object>> read(String path, String sheet, boolean head) throws IOException {
        File fs = new File(path);
        List<Map<String, Object>> res = new ArrayList<>();
        String type = path.endsWith(EXCEL2003) ? EXCEL2003 : EXCEL2007;
        if (fs.exists()) {
            InputStream is = new FileInputStream(fs);
            res = read(is, type, sheet, head);
        }

        return res;
    }

    public <T> List<T> readToList(String path, boolean head, Class<T> cls) throws IOException {
        return readToList(path, null, head, cls);
    }

    public <T> List<T> readToList(String path, String sheet, boolean head, Class<T> cls) throws IOException {
        List<Map<String, Object>> res = read(path, sheet, head);
        List<T> list = new ArrayList<>();
        for (Map<String, Object> map : res) {
            list.add(ConvertEntity.jsonToCopy(map, cls));
        }

        return list;
    }

    protected Map<String, Object> readCell(Row row, Map<String, Object> head, String prefix) {
        Map<String, Object> cell_map = new TreeMap<>();
        Short cell_index = row.getFirstCellNum();
        Cell cell = row.getCell(cell_index);
        while (row.getLastCellNum() >= cell_index) {
            if (null != cell) {
                String cell_name = String.format("%s%d", prefix, cell_index);
                String key = head.containsKey(cell_index.toString()) ? head.get(cell_index.toString()).toString() : cell_name;
                cell_map.put(key, readValue(cell, cell.getCellType()));
            }
            cell = row.getCell(cell_index++);
        }

        return cell_map;
    }

    protected Object readValue(Cell cell, CellType type){
        if (type.equals(CellType.BOOLEAN)) {
            return cell.getBooleanCellValue();
        } else if (type.equals(CellType.NUMERIC)) {
            return cell.getNumericCellValue();
        } else if (type.equals(CellType.STRING)) {
            return cell.getStringCellValue();
        } else if (type.equals(CellType.FORMULA)) {
            return readValue(cell, cell.getCachedFormulaResultType());
        } else {
            return StringUtils.EMPTY;
        }
    }

    protected Workbook createWorkbook(String type, InputStream fs) throws IOException{
        Workbook workbook = null;
        if (type.endsWith(EXCEL2007)) {
            workbook = new XSSFWorkbook(fs);
        }
        if (type.endsWith(EXCEL2003)) {
            workbook = new HSSFWorkbook(fs);
        }

        return workbook;
    }

    protected Workbook createWritebook(String type) throws IOException{
        Workbook workbook = null;
        if (type.endsWith(EXCEL2007)) {
            workbook = new XSSFWorkbook();
        }
        if (type.endsWith(EXCEL2003)) {
            workbook = new HSSFWorkbook();
        }

        return workbook;
    }

    public void write(List<Map> dataList, String sheetName, File f) throws IOException {
        // 读取Excel文档
        Workbook workBook = createWritebook(f.getCanonicalPath());
        // sheet 对应一个工作页
        Sheet sheet = workBook.createSheet(sheetName);
        try (OutputStream out = new FileOutputStream(f)) {
            int row_idx = 0;
            for (Map m : dataList) {
                int col_idx = 0;
                Row row = sheet.createRow(row_idx++);
                for (Object val : m.values()) {
                    Cell col = row.createCell(col_idx++);
                    col.setCellValue(null == val ? StringUtils.EMPTY : val.toString());
                }
            }
            workBook.write(out);
        }
    }
}
